package com.admin.model;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;

import com.admin.controller.OrdersController;
import com.admin.util.DateUtil;
import com.admin.util.Excel2007Util;
import com.admin.util.UtilTools;

public class Orders extends MyDB<Orders> {
	public static final Orders dao = new Orders();
	public Log logger = LogFactory.getLog(OrdersController.class);
	public Orders(){
		tableName = "orders";
	}
	
	// 处理导出数据
	public List<Orders> exportQuery(String[] where) {
		StringBuffer sql = new StringBuffer();
		/*sql.append(" select o.*,i.*,sp.delivery,if((o.pay_type=0),'代收','预付') payType from orders o ");
		sql.append(" inner join order_item i on o.id = i.order_id ");
		sql.append(" inner join custom_service c on o.custom_id = c.id ");
		sql.append(" left join statistics_public sp on o.adv_source_id = sp.adv_source_id ");*/
		sql.append("select * from orders");
		sql.append(" where 1 = 1 ");
		if(null != where){
			for (String w : where) {
				if(null != w){
					sql.append(" and " + w);					
				}
			}
		}
		sql.append(" order by create_date desc");
		return find(sql.toString());
	}
	
	/**
	 * 批量导出订单
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 */
	public Workbook batchExport(List<Orders> orderList){
		try {
			Workbook wb = new HSSFWorkbook();
			Sheet sheet = wb.createSheet("sheet1");
			
			CellStyle cellStyle = wb.createCellStyle();
			// 指定单元格居中对齐
			cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			// 指定单元格垂直居中对齐
			cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
			// 指定单元格自动换行
			cellStyle.setWrapText(true);

			// 设置单元格字体
			Font font = wb.createFont();
			font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
			cellStyle.setFont(font);
			
			/*String[] titles = new String[] {"成交日期","订单编号","姓名","微信昵称","电话","产品","单价","数量","优惠","总计","退款金额","退款日期","地址","症状","付款方式","客服备注","下单人","所属分组","前客服","前分组","状态(待收款/已签收)","快递","单号","审核意见","核单备注","发货日期","发货人","签收日期","经手人","客户来源","投放人"};
			Integer[] titleWidths = new Integer[] {20,20,15,15,15,15,10,10,10,10,10,20,30,30,10,20,15,15,15,15,15,15,20,30,30,20,15,20,15,15,15};
			// 列头
			Excel2007Util.createRow(titles, titleWidths, wb, sheet, 0,cellStyle);
			
			int currentRow = 1;
			for (Orders order : orderList) {
				String values[] = new String[]{
					order.getDate("create_date")+"",order.getStr("order_no"),order.getStr("name"),order.getStr("nick"),(null!=exportMobile && exportMobile.equals("1"))?order.getStr("mobile"):null,
					order.getStr("pro_name")+"",order.getDouble("pro_price")+"",order.getInt("num")+"",order.getDouble("discount")+"",order.getDouble("amount")+"",order.getDouble("return_price")+"",(order.getDate("return_date") == null?"":order.getDate("return_date")+""),
					order.getStr("address"),order.getStr("desc"),order.getStr("payType"),order.getStr("cus_desc"),order.getStr("custom_name"),order.getStr("group_name"),
					order.getStr("prev_custom_name"),order.getStr("prev_group_name"),getStateStr(order.getInt("state")),order.getStr("logistics"),order.getStr("express_no"),order.getStr("audit"),order.getStr("goods_desc"),
					order.getDate("deliver_date")+"",order.getStr("deliver_name"),order.getDate("accounted_date")+"",order.getStr("accounted_name"),order.getStr("adv_source"),order.getStr("delivery")
				};
				Excel2007Util.createRow(values, titleWidths, wb, sheet, currentRow,cellStyle);
				currentRow++;
			}*/
			String[] titles = new String[] {"订单编号","产品名称","购买数量","价格","姓名","手机","地区","地址","订单留言","快递单号","创建日期","订单来源","订单状态","客户类型"};
			Integer[] titleWidths = new Integer[] {30,30,10,10,15,20,30,30,30,30,30,30,10,10};
			// 列头
			Excel2007Util.createRow(titles, titleWidths, wb, sheet, 0,cellStyle);
			int currentRow = 1;
			for(Orders order : orderList){
				String values[] = new String[]{
					order.get("order_no"),order.get("pro_name"),order.getInt("num")+"",order.getDouble("price")+"",order.get("name"),order.get("mobile"),order.get("area"),order.get("address"),order.get("remark"),order.get("order_number"),order.getDate("create_date")+"",order.get("source"),getState(order.getInt("state")),getXian(order.getInt("xian"))	
				};
				Excel2007Util.createRow(values, titleWidths, wb, sheet, currentRow,cellStyle);
				currentRow++;
			}
			return wb;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public String getState(int state){
		String new_state="";
		if(state==0){
			new_state="待审核";
		}else if(state==1){
			new_state="待发货";
		}else if(state==2){
			new_state="已发货";
		}else if(state==3){
			new_state="已签收";
		}else if(state==4){
			new_state="已回款";
		}else if(state==5){
			new_state="未审过";
		}else if(state==6){
			new_state="待联系";
		}else if(state==7){
			new_state="已拒收";
		}else{
			new_state="未知";
		}
		return new_state;
	}
	
	public String getXian(int state){
		String new_state="";
		if(state==1){
			new_state="一线";
		}else if(state==2){
			new_state="二线";
		}else if(state==3){
			new_state="三线";
		}else{
			new_state="0";
		}
		return new_state;
	}
	
	// 获取投放渠道
	public String getQudao(HttpServletRequest request){
		String qudao = "";
		String url = UtilTools.getUrl(request);
		int startIndex = url.indexOf("q=");
		if(url.indexOf("&") != -1){
			qudao = url.substring(startIndex+2,url.indexOf("&"));
		}else if(url.indexOf("#") != -1){
			qudao = url.substring(startIndex+2,url.indexOf("#"));
		}else{
			qudao = url.substring(startIndex+2);
		}
		return qudao;
	}
	
	// 导入订单
	public String batchImport(String path) {
		String message = "";
		File importFile = new File(path);
		InputStream is = null;
		HSSFWorkbook wb = null;
		try {
			is = new FileInputStream(importFile);
			wb = new HSSFWorkbook(is);
			HSSFSheet sheet = wb.getSheetAt(0);
			// 获得总行数
			int rows = sheet.getLastRowNum();
			StringBuffer msg = new StringBuffer();
			// 遍历所有行
			for (int i = 1; i <= rows; i++) {
				HSSFRow row = sheet.getRow(i);
				if(row == null){
					continue;
				}
				// excel里的订单编号
				String orderNo = UtilTools.getCellValue(row.getCell(0));
				// 查询数据库是否有这条订单信息
				Orders orders = Orders.dao.queryModel(new String[]{"order_no = '"+orderNo+"'"});
				if(null == orders){
					msg.append(orderNo+",");
					continue;
				}
				// excel里的快递单号
				String expressNo = UtilTools.getCellValue(row.getCell(9));
				// excel里的状态
				String stateStr = UtilTools.getCellValue(row.getCell(12));
				// 只有原来订单状态为待发货，才更改状态
				if(orders.getInt("state") == 1){
					if("已发货".equals(stateStr)){
						orders.set("state",2);
						orders.set("order_number",expressNo);
						orders.update();
					}else{
						msg.append(orderNo+",");
					}
				}else{
					msg.append(orderNo+",");
				}
			}
			if(msg.length() <= 0){
				message = "导入成功！";
			}else{
				message = "导入成功！编号为："+msg.toString()+"的订单没有更新状态，请自行核查";
			}
		} catch (Exception e) {
			message = "导入订单时发生异常："+e.getMessage();
			e.printStackTrace();
			throw new RuntimeException(message);
		}finally{
			FileUtils.deleteQuietly(importFile);  // 导入完成删除文件
			IOUtils.closeQuietly(is);
		}
		return message;
	}
}
